package com.dsrk.sysusage;

import java.io.*;
import javax.naming.*;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.sql.*;
import java.util.List;

import java.sql.*;

public class Display {
	private DataSource ds = null;

	private java.sql.Connection conn = null;

	private Statement st = null;

	public Display() {
		try {
			InitialContext ctx = new InitialContext();
			if (ds == null)
				ds = (DataSource) ctx.lookup("java:comp/env/jdbc/MyDataSource");
			if (conn == null)
				conn = ds.getConnection();
			if (st == null)
				st = conn.createStatement();
		} catch (NamingException e) {
			System.err.println(e);
			e.printStackTrace();
		} catch (SQLException e) {
			System.err.println(e);
			e.printStackTrace();
		}

	}

	public java.sql.ResultSet Query(String query) {
		ResultSet rs = null;

		System.out.println("=== Display.Query : " + query);

		try {
			rs = st.executeQuery(query);
		} catch (SQLException e) {
			System.err.println(e);
			e.printStackTrace();
		}

		return rs;
	}

	public Array QueryArray(String query) {
		ResultSet rs = null;
		Array result = null;

		rs = Query(query);
		try {
			rs.next();
			// rs.getar
			// result = rs.getArray(1);
		} catch (SQLException e) {
			System.err.println(e);
			e.printStackTrace();
		}

		return result;
	}

	public String dumpData(java.sql.ResultSet rs) throws Exception {
		StringBuffer out = new StringBuffer();

		out.append("<P ALIGN='center'><TABLE BORDER=1>");
		ResultSetMetaData rsmd = rs.getMetaData();
		int columnCount = rsmd.getColumnCount();
		// table header
		out.append("<TR>");
		for (int i = 0; i < columnCount; i++) {
			out.append("<TH>" + rsmd.getColumnLabel(i + 1) + "</TH>");
		}
		out.append("</TR>");
		// the data
		while (rs.next()) {
			out.append("<TR>");
			for (int i = 0; i < columnCount; i++) {
				out.append("<TD>" + rs.getString(i + 1) + "</TD>");
			}
			out.append("</TR>");
		}
		out.append("</TABLE></P>");

		return out.toString();
	}

	public String DisplayCpuByGroup(String groupname) throws Exception {
		String cond = "";

		if (!groupname.equals("")) {
			cond = "AND sg.groupname='"+groupname+"'";
		}

		String query = "SELECT v1 AS \"Group\", v2 AS \"Date\", AVG(v3) AS \"Avg\", MAX(v3) AS \"Max\""
				+ " FROM ("
				+ " SELECT sg.groupname, DATE(sr.timestamp), SUM(sr.value/3*0.8)"
				+ " FROM sysusage_rawdata sr, sysusage_server sg"
				+ " WHERE sr.property='cpu' AND sr.hostname = sg.hostname "+cond
				+ " GROUP BY sg.groupname, DATE(sr.timestamp), HOUR(sr.timestamp), INT(MINUTE(sr.timestamp)/10)"
				+ " ) AR1(v1, v2, v3)" + " GROUP BY v1, v2";

		return dumpData(Query(query));
	}

	public String DisplayMemByGroup(String groupname) throws Exception {
		String query = "SELECT SUBSTR(timestamp,1,10) AS \"DATE\", AVG(total/(cnt-1)*0.8) AS \"AVG\", MAX(total/(cnt-1)*0.8) AS \"MAX\""
				+ "		FROM ("
				+ "		SELECT timestamp, sum(value), count(hostname)"
				+ "	FROM ("
				+ "		SELECT CHAR(DATE(timestamp))||' '||RTRIM(CHAR(HOUR(timestamp)))||':'||RTRIM(CHAR(INT(MINUTE(timestamp)/10)))||'0', hostname, property, value"
				+ "		FROM sysusage_rawdata"
				+ "		WHERE property = 'mem'"
				+ "		 AND HOUR(timestamp)>=10 "
				+ "		 AND hostname IN (SELECT hostname FROM sysusage_server WHERE groupname='"
				+ groupname
				+ "')"
				+ "		UNION"
				+ "		SELECT CHAR(DATE(timestamp))||' 0'||RTRIM(CHAR(HOUR(timestamp)))||':'||RTRIM(CHAR(INT(MINUTE(timestamp)/10)))||'0', hostname, property, value"
				+ "		FROM sysusage_rawdata"
				+ "		WHERE property = 'mem'"
				+ "		 AND HOUR(timestamp)<10 "
				+ "		 AND hostname IN (SELECT hostname FROM sysusage_server WHERE groupname='"
				+ groupname
				+ "')"
				+ "		) AR(timestamp, hostname, property, value)"
				+ "		GROUP BY timestamp"
				+ "		) V2(timestamp, total, cnt)"
				+ "		WHERE cnt>1" + "		GROUP BY SUBSTR(timestamp,1,10)";

		return dumpData(Query(query));
	}

	public String DisplayDiskByProperty(String property) throws Exception {
		String query = "SELECT ar1.hostname, ar1.timestamp, tot, curr, curr/1.0/tot*100 as pct"
				+ " FROM "
				+ " ("
				+ " SELECT DATE(timestamp) AS timestamp, hostname, MAX(value) AS curr"
				+ " FROM sysusage_rawdata"
				+ " WHERE property = '"
				+ property
				+ "'"
				+ " GROUP BY DATE(timestamp), hostname ) ar1, ("
				+ " SELECT DATE(timestamp) AS timestamp, hostname, MAX(value) AS tot"
				+ " FROM sysusage_rawdata"
				+ " WHERE property LIKE '"
				+ property
				+ "_%'"
				+ " GROUP BY DATE(timestamp), hostname"
				+ " ) ar2"
				+ " WHERE ar1.timestamp = ar2.timestamp AND ar1.hostname = ar2.hostname"
				+ " ORDER BY ar1.hostname, ar1.timestamp";

		return dumpData(Query(query));
	}

	public String DisplayNicByGroup(String groupname) throws Exception {
		String query = "SELECT n1, n3, CHAR(t4*8/1024)||'MB', n4, n4/1.0/t4*100 AS PCT"
				+ " FROM ("
				+ "     SELECT '"
				+ groupname
				+ "', PROPERTY, CHAR(DATE(TIMESTAMP)), MAX(VALUE)"
				+ " FROM SYSUSAGE_RAWDATA"
				+ " WHERE PROPERTY = 'nic' AND hostname IN ( SELECT hostname FROM sysusage_server WHERE groupname LIKE '%"
				+ groupname
				+ "%' )"
				+ " GROUP BY PROPERTY, CHAR(DATE(TIMESTAMP))) NIC(n1, n2, n3, n4),"
				+ " ("
				+ " SELECT '"
				+ groupname
				+ "', PROPERTY, CHAR(DATE(TIMESTAMP)), MAX(VALUE)"
				+ " FROM SYSUSAGE_RAWDATA"
				+ " WHERE PROPERTY = 'nic_tot' AND hostname IN ( SELECT hostname FROM sysusage_server WHERE groupname LIKE '%"
				+ groupname
				+ "%' )"
				+ " GROUP BY PROPERTY, CHAR(DATE(TIMESTAMP))) NIC_TOT(t1, t2, t3, t4)"
				+ " WHERE n1=t1 AND n3=t3" + " ORDER BY n1, n3";

		return dumpData(Query(query));
	}
}
